
import pandas as pd
import numpy as np
import datetime
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import seaborn as sns

# 数据库连接
def get_db_connection():
    """建立数据库连接数据库"""
    engine = create_engine('mysql+pymysql://root:root@localhost:3306/insurance_db')
    return engine

# 数据获取
def get_cost_data(engine, area=None, start_date=None, end_date=None):
    """
    获取成本费用数据

    参数:
    - engine: 数据库连接引擎
    - area: 区域，默认为None表示所有区域
    - start_date: 开始日期
    - end_date: 结束日期

    返回:
    - 成本费用数据DataFrame
    """
    # 基础查询语句
    query = """
    SELECT 
        pic.cost_id,
        pic.policy_id,
        pic.cost_date,
        pic.cost_type,
        pic.cost_amount,
        pic.payment_status,
        pic.area,
        pic.organization_id,
        pic.written_year,
        p.premium_amount,
        pi.earned_premium
    FROM property_insurance_cost pic
    JOIN policy p ON pic.policy_id = p.policy_id
    JOIN premium_income pi ON pic.policy_id = pi.policy_id
    """

    # 条件过滤
    conditions = []
    params = []  # 改为列表存储参数值

    if area:
        conditions.append("pic.area = %s")  # 改为%s占位符
        params.append(area)  # 添加参数值
    if start_date:
        conditions.append("pic.cost_date >= %s")
        params.append(start_date)
    if end_date:
        conditions.append("pic.cost_date <= %s")
        params.append(end_date)

    # 添加条件
    if conditions:
        query += " WHERE " + " AND ".join(conditions)

    # 执行查询（注意参数传递方式）
    df = pd.read_sql(query, engine, params=params)  # 直接传递参数列表

    # 转换日期类型
    df['cost_date'] = pd.to_datetime(df['cost_date'])

    return df


def get_claim_expense_data(engine, area=None, start_date=None, end_date=None):
    """获取理赔费用数据"""
    query = """
    SELECT 
        pce.expense_id,
        pce.claim_id,
        pce.expense_type,
        pce.expense_amount,
        pce.payment_date,
        p.area,
        cp.paid_amount
    FROM property_claim_expense pce
    JOIN claim_payment cp ON pce.claim_id = cp.claim_id
    JOIN policy p ON cp.policy_id = p.policy_id
    """

    # 条件过滤
    conditions = []
    params = []

    if area:
        conditions.append("p.area = %s")
        params.append(area)
    if start_date:
        conditions.append("pce.payment_date >= %s")
        params.append(start_date)
    if end_date:
        conditions.append("pce.payment_date <= %s")
        params.append(end_date)

    # 添加条件
    if conditions:
        query += " WHERE " + " AND ".join(conditions)

    # 执行查询
    df = pd.read_sql(query, engine, params=params)
    df['payment_date'] = pd.to_datetime(df['payment_date'])

    return df


def get_marketing_expense_data(engine, area=None, start_date=None, end_date=None):
    """获取营销费用数据"""
    query = """
    SELECT 
        pme.marketing_id,
        pme.organization_id,
        pme.expense_date,
        pme.expense_type,
        pme.expense_amount,
        pme.related_products,
        pme.area
    FROM property_marketing_expense pme
    """

    # 条件过滤
    conditions = []
    params = []

    if area:
        conditions.append("pme.area = %s")
        params.append(area)
    if start_date:
        conditions.append("pme.expense_date >= %s")
        params.append(start_date)
    if end_date:
        conditions.append("pme.expense_date <= %s")
        params.append(end_date)

    # 添加条件
    if conditions:
        query += " WHERE " + " AND ".join(conditions)

    # 执行查询
    df = pd.read_sql(query, engine, params=params)
    df['expense_date'] = pd.to_datetime(df['expense_date'])

    return df

# 指标计算
def calculate_cost_indicators(cost_df, claim_expense_df, marketing_df):
    """
    计算成本费用指标

    参数:
    - cost_df: 成本费用数据
    - claim_expense_df: 理赔费用数据
    - marketing_df: 营销费用数据

    返回:
    - 指标计算结果字典
    """
    # 1. 总费用金额
    total_cost = cost_df['cost_amount'].sum()

    # 2. 各项费用金额及占比
    cost_by_type = cost_df.groupby('cost_type')['cost_amount'].sum().reset_index()
    cost_by_type['percentage'] = cost_by_type['cost_amount'] / total_cost * 100

    # 3. 总保费收入
    total_premium = cost_df['premium_amount'].sum()

    # 4. 已赚保费
    total_earned_premium = cost_df['earned_premium'].sum()

    # 5. 费用率 = 总费用 ÷ 保费收入 × 100%
    expense_ratio = (total_cost / total_premium * 100) if total_premium > 0 else 0

    # 6. 手续费率
    commission_cost = cost_df[cost_df['cost_type'] == '手续费']['cost_amount'].sum()
    commission_ratio = (commission_cost / total_premium * 100) if total_premium > 0 else 0

    # 7. 理赔费用率 = 理赔费用 ÷ 已付赔款 × 100%
    total_claim_expense = claim_expense_df['expense_amount'].sum()
    total_paid_claim = claim_expense_df['paid_amount'].sum()
    claim_expense_ratio = (total_claim_expense / total_paid_claim * 100) if total_paid_claim > 0 else 0

    # 8. 单位保费费用
    cost_per_premium = (total_cost / total_premium) if total_premium > 0 else 0

    # 9. 费用收入比 = 费用总额 ÷ 已赚保费 × 100%
    cost_income_ratio = (total_cost / total_earned_premium * 100) if total_earned_premium > 0 else 0

    # 10. 营销费用占比
    total_marketing_cost = marketing_df['expense_amount'].sum()
    marketing_ratio = (total_marketing_cost / total_cost * 100) if total_cost > 0 else 0

    # 整理结果
    indicators = {
        'total_cost': total_cost,
        'total_premium': total_premium,
        'total_earned_premium': total_earned_premium,
        'expense_ratio': expense_ratio,
        'commission_ratio': commission_ratio,
        'claim_expense_ratio': claim_expense_ratio,
        'cost_per_premium': cost_per_premium,
        'cost_income_ratio': cost_income_ratio,
        'marketing_ratio': marketing_ratio,
        'cost_by_type': cost_by_type
    }

    return indicators

# 时间维度聚合
def aggregate_by_time(df, date_col, freq='M'):
    """
    按时间维度聚合数据

    参数:
    - df: 输入数据
    - date_col: 日期列名
    - freq: 聚合频率，'D'=日, 'W'=周, 'M'=月, 'Y'=年

    返回:
    - 聚合后的数据
    """
    # 设置日期为索引
    df = df.copy()
    df.set_index(date_col, inplace=True)

    # 按频率聚合
    aggregated = df.resample(freq)['cost_amount'].sum().reset_index()

    # 重命名列
    aggregated.columns = [date_col, 'total_cost']

    return aggregated

# 指标计算与可视化
def analyze_and_visualize(area=None, start_date=None, end_date=None):
    """
    完整的成本费用分析流程

    参数:
    - area: 区域
    - start_date: 开始日期
    - end_date: 结束日期
    """
    # 1. 连接数据库
    engine = get_db_connection()

    # 2. 获取数据
    cost_df = get_cost_data(engine, area, start_date, end_date)
    claim_expense_df = get_claim_expense_data(engine, area, start_date, end_date)
    marketing_df = get_marketing_expense_data(engine, area, start_date, end_date)

    # 3. 计算指标
    indicators = calculate_cost_indicators(cost_df, claim_expense_df, marketing_df)

    # 4. 打印关键指标
    print(f"区域: {area if area else '所有区域'}")
    print(f"时间范围: {start_date} 至 {end_date}")
    print(f"总费用金额: {indicators['total_cost']:.2f} 元")
    print(f"总保费收入: {indicators['total_premium']:.2f} 元")
    print(f"费用率: {indicators['expense_ratio']:.2f}%")
    print(f"手续费率: {indicators['commission_ratio']:.2f}%")
    print(f"理赔费用率: {indicators['claim_expense_ratio']:.2f}%")
    print(f"费用收入比: {indicators['cost_income_ratio']:.2f}%")

    # 5. 可视化 - 费用构成（增加空数据判断）
    plt.figure(figsize=(10, 6))
    if not indicators['cost_by_type'].empty:  # 检查数据是否为空
        sns.barplot(x='cost_type', y='cost_amount', data=indicators['cost_by_type'])
        plt.title('费用构成分析')
        plt.xlabel('费用类型')
        plt.ylabel('金额(元)')
        plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()
    else:
        print("警告：没有可用的费用类型数据，无法绘制费用构成图表")

    # 6. 可视化 - 时间趋势（按周、月、年）（同样增加空数据判断）
    # 按周
    weekly_data = aggregate_by_time(cost_df, 'cost_date', 'W')
    plt.figure(figsize=(12, 6))
    if not weekly_data.empty:
        sns.lineplot(x='cost_date', y='total_cost', data=weekly_data)
        plt.title('每周费用趋势')
        plt.xlabel('日期')
        plt.ylabel('总费用(元)')
        plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()
    else:
        print("警告：没有可用的周度费用数据，无法绘制周度趋势图表")

    # 按月
    monthly_data = aggregate_by_time(cost_df, 'cost_date', 'M')
    plt.figure(figsize=(12, 6))
    if not monthly_data.empty:
        sns.lineplot(x='cost_date', y='total_cost', data=monthly_data)
        plt.title('每月费用趋势')
        plt.xlabel('日期')
        plt.ylabel('总费用(元)')
        plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()
    else:
        print("警告：没有可用的月度费用数据，无法绘制月度趋势图表")

    # 7. 区域对比（如果分析所有区域）
    if not area:
        area_cost = cost_df.groupby('area')['cost_amount'].sum().reset_index()
        plt.figure(figsize=(10, 6))
        sns.barplot(x='area', y='cost_amount', data=area_cost)
        plt.title('区域费用对比')
        plt.xlabel('区域')
        plt.ylabel('总费用(元)')
        plt.tight_layout()
        plt.show()

    return indicators

# 执行分析示例
if __name__ == "__main__":
    # 分析北京区域2023年数据
    analyze_and_visualize(area='北京', start_date='2023-01-01', end_date='2023-12-31')

    # 分析所有区域2023年数据
    # analyze_and_visualize(start_date='2023-01-01', end_date='2023-12-31')